from flask import Flask,render_template
from flask_sqlalchemy import SQLAlchemy
import sqlalchemy
from flask_migrate import Migrate

host = "127.0.0.1"
port = 3306
username = "root"
password = "123456"
databases = "xihu"

URI = f"mysql+pymysql://{username}:{password}@{host}:{port}/{databases}?charset=utf8"

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = URI
app.config["SQLALCHEMY_TRACK_MODIFICATION"] = False
# app.config.from_object("config")
db = SQLAlchemy(app)

# sql语句需要在app_context中执行
with app.app_context():
    # 创建sql的链接
    with db.engine.connect() as conn:
        # 执行查询语句
        re = conn.execute(sqlalchemy.text("select 1"))
        # 打印执行结果
        print("查询结果为：" + str(re.fetchone()))


# 用ORM方式查询供应商数据
@app.route("/compare")
def compare_list():
    # 防止数据库操作出错，使用异常处理
    try:
        with app.app_context():
            compare = Compare.query.all()
            name = "柯莱特公司"
            return render_template("test.html",name=name,compara_name=compare)
    except Exception as e:
        return f"a error occurred: {str(e)}",500
    

# 用ORM方式根据ID查询供应商数据
@app.route("/compare/<id>")
def compare_by_id(id):
    # 防止数据库操作出错，使用异常处理
    try:
        with app.app_context():
            compare = Compare.query.get(id)
            return render_template("compare.html",name=compare.name,info=compare.city)
    except Exception as e:
        return f"a error occurred: {str(e)}", 404



class Compare(db.Model):
    __tablename__ = "compare"
    id = db.Column(db.Integer,autoincrement=True,primary_key=True)
    name = db.Column(db.String(30),nullable=False)
    city = db.Column(db.String(20))


# with app.app_context():
#     # 删除重db建表格
#     db.drop_all()
#     db.create_all()
#     print("重建表格成功")

if __name__ == "__main__":
    with app.app_context():
        # compare1 = Compare(id=1,name="移动1",city="北京1")
        # compare2 = Compare(id=2,name="移动2",city="北京2")
        # compare3 = Compare(id=3,name="移动3",city="北京3")
        # compare4 = Compare(id=4,name="移动4",city="北京4")
        # compare5 = Compare(id=5,name="移动5",city="北京5")
        # compare6 = Compare(id=6,name="移动6",city="北京6")
        # compare7 = Compare(id=7,name="移动7",city="北京7")
        # db.session.add_all([compare1,compare2,compare3,compare4,compare5,compare6,compare7])
        # db.session.commit()
        list1 = Compare.query.all()
        for compare in list1:
            print(compare.name)
